SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROC [dbo].[PZaregWordSELECT]
@ik_year_uch_pl INT,
@ik_sem_type INT,
@Ik_grup INT,
@nCode NUMERIC,
@iK_disc int,
@iK_vid_zanyat int,
@DateReg1 datetime,
@DateReg2 datetime
AS
SELECT distinct
Options[Вариант],
UD.idUspevDocs[idРаботы],
S.nCode[isСтудента],
Per.Cfirstname[Фамилия],
Per.Clastname[Имя],
Per.Cotch[Отчество],
Z.Ik_zach[idзачетки],
Nn_zach[Зачетка],
KPTheme[Тема],
NReg[Регистрационный №],
DateReg[Дата регистрации]
from dbo.Person pr
inner join dbo.Prepod P on pr.nCode = p.nCode
inner join dbo.Relation_kafedra_prep Rp on p.itab_n=Rp.itab_n 
inner join dbo.Prepod_plan_nagr ppn on rp.ik_id_prepod = ppn.ik_id_prepod
inner join dbo.Content_plan_nagr cpn on ppn.ik_content_plan_nagr = cpn.ik_content_plan_nagr
inner join dbo.vid_zaniat vz on cpn.ik_vid_zanyat = vz.iK_vid_zanyat
inner join dbo.Plan_nagr pn on cpn.ik_plan_nagr = pn.ik_plan_nagr
inner join dbo.SemesterType ST on pn.ik_sem_type=ST.ik_sem_type
inner join dbo.Year_uch_pl Yup on pn.ik_year=Yup.ik_year_uch_pl
inner join dbo.kafedra kf on pn.ik_kaf = kf.ik_kaf
inner join dbo.Content_UchPl cup on kf.ik_kaf = cup.ik_kaf
inner join dbo.discpln ds on cpn.ik_disc = ds.iK_disc
inner join dbo.Calc_unit_semestr cus on ppn.ik_calc_unit = cus.ik_calc_unit
inner join dbo.Calc_unit_grup cug on cus.ik_calc_unit = cug.ik_calc_unit
inner join dbo.Grup g on cug.Ik_grup = g.Ik_grup
inner join dbo.StudGrup SG on g.Ik_grup=SG.Ik_grup
inner join dbo.Zach Z on SG.Ik_zach=Z.Ik_zach
inner join dbo.UspevDocument UD on UD.ik_zach=Z.Ik_zach
inner join dbo.UspevKPTheme UT on UD.idUspevDocs=UT.idUspevDocs
inner join dbo.Student S on Z.nCode=S.nCode
inner join dbo.Person Per on S.nCode=Per.nCode
Where @ik_year_uch_pl=Yup.ik_year_uch_pl AND pr.nCode=@nCode AND @Ik_grup=g.Ik_grup 
AND @ik_sem_type=ST.ik_sem_type AND @iK_disc=ds.iK_disc and @iK_vid_zanyat=vz.iK_vid_zanyat and DateReg BETWEEN @DateReg1 AND @DateReg2

GO
